package com.axioma.dbmaint.datalayer.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.axioma.dbmaint.datalayer.dao.DatabaseRepository;
import com.axioma.dbmaint.datalayer.dto.DatabaseDTO;
import com.axioma.dbmaint.datalayer.dto.ServerDTO;
import com.axioma.dbmaint.datalayer.dto.util.DBVendor;
import com.axioma.dbmaint.datalayer.dto.util.Status;
import com.axioma.dbmaint.datalayer.util.datasource.DataSourceFactory;
import com.axioma.dbmaint.datalayer.util.datasource.impl.PGDataSourceFactory;
import com.axioma.dbmaint.datalayer.util.datasource.impl.SSDataSourceFactory;

public class JDBCDatabaseRepositoryImpl implements DatabaseRepository {
   
   // This one is a bit weird, because we're going to query the database, 
   // but which database we're going to query is determiend on each
   // call
   
   @Override
   public List<DatabaseDTO> getDatabasesOnServer(ServerDTO serverDTO) {
      List<DatabaseDTO> databaseDTOs = new ArrayList<DatabaseDTO>();
      
      // Get connected to our target server's master database
      DataSource masterDataSource = null;
      try {
         masterDataSource = getDataSource(serverDTO);
         
         // Set up a JDBCTemplate for that DataSource
         JdbcTemplate masterJdbcTemplate = new JdbcTemplate(masterDataSource);
         
         List<String> databases = masterJdbcTemplate.query(findAllDatabases(serverDTO), new RowMapper<String>() {
            public String mapRow(ResultSet rs, int i) throws SQLException {
               return rs.getString(1);
            }
         });
         
         for (String database : databases) {
            // If this is a database we should skip, go round again
            if (skipDatabase(serverDTO.getVendor(), database))
               continue;
            
            ServerDTO dbSpecificServerDTO = buildDBSpecificServerDTO(serverDTO, database);
            
            // Build a datasource for the specific database we found
            DataSource dbDataSource = getDataSource(dbSpecificServerDTO);
            JdbcTemplate dbJdbcTemplate = new JdbcTemplate(dbDataSource);

            String schemaVersion = "unknown";
            try {
               schemaVersion = getSchemaVersion(dbJdbcTemplate);
            } catch (Exception e) {
               Throwable cause = e.getCause();
               if (cause != null && cause.getMessage().contains("relation"))
                  continue;  // No schema_version table, or other issues, so it's not an Axioma 7x database; skip it
               else if (e instanceof EmptyResultDataAccessException)
                  continue; // schema_version was there, but empty
               else {
                  System.err.println("Couldn't get info on database " + database);
                  continue;
               }
            } finally {
               closeDataSource(dbDataSource);
            }
            
            // How many tables?
            Integer numTables=0;
            // DBsize has to come from the master db
            Integer dbSizeMB=0;
            try {
               numTables = getNumberOfTables(dbJdbcTemplate, database);
               dbSizeMB = getDatabaseSize(masterJdbcTemplate, dbJdbcTemplate, serverDTO, database);
            } catch (Exception e) {
               // If we can't get the size of it or count the tables, something's wrong...skip it
               System.out.println("Skipped database " + database + " because we could not count tables or get size");
               continue;
            }
            
            // If we get here, then we found a schema version!
            DatabaseDTO dbDTO = new DatabaseDTO(dbSpecificServerDTO.getServerName(), database, schemaVersion, dbSizeMB, numTables);
            databaseDTOs.add(dbDTO);
         }
      } finally {
         closeDataSource(masterDataSource);
      }
      return databaseDTOs;
   }
   
   @Override
   public void addDatabase(ServerDTO serverDTO, DatabaseDTO databaseDTO) {
      // TODO Auto-generated method stub
      
   }

   @Override
   public void resetDatabase(ServerDTO serverDTO, DatabaseDTO databaseDTO) {
      // TODO Auto-generated method stub
      
   }

   @Override
   public void copyDatabase(ServerDTO serverDTO, DatabaseDTO sourceDatabaseDTO,
         DatabaseDTO targetDatabaseDTO) {
      // TODO Auto-generated method stub
      
   }
   
   // These are regex 
   private static final String[] PG_SKIP = {"template0", "template1", "postgres"};
   private static final String[] SS_SKIP = {"master", "tempdb", "model", "msdb", "ReportServer.*"};
   private boolean skipDatabase(DBVendor vendor, String db) {
      String[] skip = null;
      switch (vendor) {
      case POSTGRESQL:
         skip = PG_SKIP;
         break;
      case SQLSERVER:
         skip = SS_SKIP;
         break;
      }
      
      for (String sysTable : skip) {
         if (db.matches(sysTable))
            return true;
      }
      
      return false;
   }
   
   private void closeDataSource(DataSource ds) {
      
      try {
         if (ds != null) {
            Connection c = ds.getConnection();
            if (c != null && !c.isClosed()) {
               c.close();
            }
         }
      } catch (Exception e) { /* Hide these */ }
   }

   
   private String getSchemaVersion(JdbcTemplate jdbcTemplate) {
      String version = jdbcTemplate.queryForObject("SELECT minor FROM schema_version", String.class);
      return version;
   }
   
   private Integer getNumberOfTables(JdbcTemplate jdbcTemplate, String dbName) {
      String countTables = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_catalog=?";
      Integer numTables = jdbcTemplate.queryForObject(countTables, new Object[] {dbName} , Integer.class);
      return numTables;
   }
   

   
   private ServerDTO buildDBSpecificServerDTO(ServerDTO serverDTO, String database) {
      return new ServerDTO(serverDTO.getServerName(), serverDTO.getPort(),
            serverDTO.getVendor(), database, Status.UNKNOWN, serverDTO.getUser(), serverDTO.getPassword()); 
   }
   
   private String findAllDatabases(ServerDTO serverDTO) {
      DBVendor vendor = serverDTO.getVendor();
      String sql = null;
      switch (vendor) {
      case SQLSERVER:
         // Snapshot databases are not real dbs, so avoid those by finding dbs with only null source_db_id
         sql = "select name as dbname from sys.databases where source_database_id is null";
         break;

      case POSTGRESQL:
         sql = "select datname as dbname from pg_database";
         break;
      }
      return sql;
   }
   
   private Integer getDatabaseSize(JdbcTemplate masterJdbcTemplate, JdbcTemplate dbJdbcTemplate, ServerDTO serverDTO, String database) {
      String qry = getDatabaseSizeQuery(serverDTO);
      
      JdbcTemplate jdbcTemplateToUse;
      
      if (serverDTO.getVendor() == DBVendor.POSTGRESQL)
         jdbcTemplateToUse = masterJdbcTemplate;
      else jdbcTemplateToUse = dbJdbcTemplate;
      
      Integer sizeMB = jdbcTemplateToUse.queryForObject(qry, new Object[] {database}, Integer.class);
      return sizeMB;
   }
   
   private static final String SS_DB_SIZE_QUERY = 
         "with fs " +
         "as " +
         "(" +
         "  select database_id, type, size * 8.0 / 1024 size " +
         "  from sys.master_files" +
         ")" +
         "select " + 
         "((select sum(size) from fs where type = 0 and fs.database_id = db.database_id) + " + 
         "(select sum(size) from fs where type = 1 and fs.database_id = db.database_id)) " +
         "from sys.databases db where name=?";
   
   private static final String PG_DB_SIZE_QUERY = 
   		"select pg_database_size(?)/(1024*1024)";
   
   private String getDatabaseSizeQuery(ServerDTO serverDTO) {
      DBVendor vendor = serverDTO.getVendor();
      String sql = null;
      switch (vendor) {
      case SQLSERVER:
         sql = SS_DB_SIZE_QUERY;
         break;

      case POSTGRESQL:
         sql = PG_DB_SIZE_QUERY;
         break;
      }
      return sql;
   }
   
   private DataSource getDataSource(ServerDTO serverDTO) {
      
      // There's probably a Springy way to this...would like to have a factory object
      // set for us by Spring (requiring this object to be a prototype, not singleton)
      // based on the server info.
      
      // For now, we'll do this old school...
      DataSourceFactory dsFactory = serverDTO.getVendor()==DBVendor.SQLSERVER ? new SSDataSourceFactory() : new PGDataSourceFactory();
      return dsFactory.buildDataSource(serverDTO);
   }
}
